import json
import pymysql

# 1、读取学生表的数据

with open("../data/students.json", mode="r", encoding="UTF-8") as file:
    # 读取数据去掉前后的空格和制表符换行符
    students = [stu.strip() for stu in file.readlines()]

# 2、解析json格式的数据
students = [json.loads(stu) for stu in students]

# 3、创建数据库连接
connect = pymysql.connect(host="cdh01", port=3306, user="root", passwd="123456", database="bigdata25")

# 获取游标
cursor = connect.cursor()

# 创建表
cursor.execute("""
create table if not exists students(
    id varchar(20),
    name varchar(10),
    age int,
    sex varchar(10),
    clazz varchar(10)
)
""")

# 循环将数据写入数据库
for student in students:
    id = student["id"]
    name = student["name"]
    age = student["age"]
    sex = student["gender"]
    clazz = student["clazz"]
    cursor.execute(f"insert into students values('{id}','{name}',{age},'{sex}','{clazz}')")

connect.commit()

connect.close()
